﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using MySql.Data.MySqlClient;

namespace ZHDJ.Data.Models.Common
{
    /// <summary>
    /// 积分详细
    /// </summary>
    public class PartyObjectScore : EntityBase
    {
        #region 属性
        /// <summary>
        /// 获得积分的人/组织ID
        /// </summary>
        public string ObjectID { get; set; }

        /// <summary>
        /// Member人员，Branch组织
        /// </summary>
        public string ObjectType { get; set; }

        /// <summary>
        /// 获得时间
        /// </summary>
        public DateTime? ScoreDate { get; set; }

        /// <summary>
        /// 积分类型
        /// </summary>
        public string Category { get; set; }

        /// <summary>
        /// 积分
        /// </summary>
        public decimal Score { get; set; }

        /// <summary>
        /// 积分来源（保留字段）
        /// </summary>
        public string SourceType { get; set; }

        /// <summary>
        /// 创建人
        /// </summary>
        public string CreateBy { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 关联ID(关联产生积分的业务ID)
        /// </summary>
        public string ForeignID { get; set; }

        /// <summary>
        /// 扩展标识
        /// </summary>
        public string Flag { get; set; }

        /// <summary>
        /// 积分所属方式（标识）
        /// </summary>
        public string ScoreType { get; set; }

        /// <summary>
        /// 积分描述
        /// </summary>
        public string Description { get; set; }
        #endregion

        /// <summary>
        /// 个人积分 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="category"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetGridDataMember(int pageIndex, int pageSize, string category, string searchKey = "", string loginId = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  data.*,m.UserName name,m2.UserName CreateByName,r.Name CategoryName,
                        case data.ObjectType when 'Branch' then '组织积分' else '个人积分' end as  ObjectTypeName
                        from tblPartyObjectScore data
                        inner join tblpartyorgmember m on(m.LoginID= data.ObjectID)
                        inner join tblpartyorgmember m2 on(m2.LoginID= data.CreateBy)
                        inner join tblPartyObjectScoreRule r on(r.objectType='Member' and r.Category=data.Category)
                        where data.objectType='Member'  ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and m.UserName like ?key ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "key", Value = string.Format("%{0}%", searchKey) });
            }

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and data.Category=?Category");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Category", Value = category });
            }
            if (!string.IsNullOrEmpty(loginId))
            {
                sb.Append(" and data.ObjectId=?ObjectId");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ObjectId", Value = loginId });
            }
            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 组织积分
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="category"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataSet GetGridDataBranch(int pageIndex, int pageSize, string category,string BranchID, string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  data.*,b.BranchName as Name,m2.UserName CreateByName,r.Name CategoryName,
                        case data.ObjectType when 'Branch' then '组织积分' else '个人积分' end as  ObjectTypeName
                        from tblPartyObjectScore data
                        inner join tblpartyorgbranch b on (b.branchid=data.objectid) 
                        inner join tblpartyorgmember m2 on(m2.LoginID= data.CreateBy)
                        inner join tblPartyObjectScoreRule r on(r.objectType='Branch' and r.Category=data.Category)
                        where   data.objectType='Branch' ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and b.BranchName like ?key ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "key", Value = string.Format("%{0}%", searchKey) });
            }

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and data.Category=?Category");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Category", Value = category });
            }
            if (BranchID!="") {
                sb.Append(" and data.ObjectID='" + BranchID+"'");
            }
            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 个人论坛积分 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="loginId"></param>
        /// <returns></returns>
        public static DataSet GetGridDataMemberForum(int pageIndex, int pageSize, string loginId)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  data.*,m.UserName name,m2.UserName CreateByName,r.Name CategoryName,
                        case data.ObjectType when 'Branch' then '组织积分' else '个人积分' end as  ObjectTypeName, f.ForumTitle
                        from tblPartyObjectScore data
                        inner join tblpartyorgmember m on(m.LoginID= data.ObjectID)
                        inner join tblpartyorgmember m2 on(m2.LoginID= data.CreateBy)
                        inner join tblPartyObjectScoreRule r on(r.objectType='Member' and r.Category=data.Category)
                        left join tblpartyforum f on(f.id=data.ForeignID) 
                        where data.objectType='Member'  ");
            sb.Append(" and data.Category like 'Forum%' ");
            if (!string.IsNullOrEmpty(loginId))
            {
                sb.Append(" and data.ObjectId=?ObjectId");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ObjectId", Value = loginId });
            }
            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 根据所属人获取积分总数
        /// </summary>
        /// <param name="ObjectType">积分类型（组织积分、个人积分）</param>
        /// <param name="CreateBy">获得积分的人/组织ID</param>
        /// <returns></returns>
        public static double SumMemberintegral(string ObjectType, string ObjectID)
        {
            double sum = 0;

            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() { DbType=DbType.String,ParameterName="ObjectType",Value=ObjectType},
                new MySqlParameter() { DbType=DbType.String,ParameterName="ObjectID",Value=ObjectID}
            };

            StringBuilder sb = new StringBuilder();
            sb.Append(" select SUM(Score) from tblpartyobjectscore where ObjectType=@ObjectType AND ObjectID=@ObjectID ");

            object ob = MySqlHelper.ExecuteScalar(sb.ToString(), parameters.ToArray());
            if (ob != null)
                sum = Convert.ToDouble(ob);

            return sum;
        }

        /// <summary>
        /// 获取当前所属人的总排名
        /// </summary>
        /// <param name="ObjectType"></param>
        /// <param name="ObjectID"></param>
        /// <returns></returns>
        public static int GetRankingByObjectID(string ObjectType, string ObjectID)
        {
            int rank = 0;

            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() { DbType=DbType.String,ParameterName="ObjectType",Value=ObjectType},
                new MySqlParameter() { DbType=DbType.String,ParameterName="ObjectID",Value=ObjectID}
            };

            StringBuilder sb = new StringBuilder();
            sb.Append(@" SELECT rowobj.rownum FROM
                        (SELECT obj.ObjectID,SumScore,
                         CASE 
                         WHEN @rowtotal = obj.SumScore THEN
                              @rownum
                         WHEN @rowtotal := obj.SumScore THEN
                              @rownum :=@rownum + 1
                         WHEN @rowtotal = 0 THEN
                              @rownum :=@rownum + 1
                         END AS rownum
                         FROM 
                        (SELECT ObjectID,Score,SUM(Score) AS SumScore FROM tblpartyobjectscore WHERE ObjectType=@ObjectType  
                         GROUP BY ObjectID ORDER BY SUM(Score) DESC ) AS obj,
                        (SELECT @rownum := 0 ,@rowtotal := NULL) r
                        ) AS rowobj WHERE rowobj.ObjectID=@ObjectID ");

            object ob = MySqlHelper.ExecuteScalar(sb.ToString(),parameters.ToArray());
            if (ob != null)
                rank = Convert.ToInt32(ob);

            return rank;
        }

        /// <summary>
        /// 获取当前所属人的组织内排名
        /// </summary>
        /// <param name="ObjectType"></param>
        /// <param name="ObjectID"></param>
        /// <param name="BranchID"></param>
        /// <returns></returns>
        public static int GetRankingByObjectID(string ObjectType, string ObjectID, string BranchID)
        {
            int rank = 0;

            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter() { DbType=DbType.String,ParameterName="ObjectType",Value=ObjectType},
                new MySqlParameter() { DbType=DbType.String,ParameterName="ObjectID",Value=ObjectID},
                new MySqlParameter() { DbType=DbType.String,ParameterName="BranchID",Value=BranchID}
            };

            StringBuilder sb = new StringBuilder();
            sb.Append(@" SELECT rowobj.rownum FROM
                        (SELECT obj.ObjectID,SumScore,
                         CASE 
                         WHEN @rowtotal = obj.SumScore THEN
                              @rownum
                         WHEN @rowtotal := obj.SumScore THEN
                              @rownum :=@rownum + 1
                         WHEN @rowtotal = 0 THEN
                              @rownum :=@rownum + 1
                         END AS rownum
                         FROM (select ObjectID,SUM(Score) AS SumScore from tblpartyorgmember as m INNER JOIN tblpartyobjectscore as s on m.LoginID = s.ObjectID  
                         WHERE ObjectType=@ObjectType and BranchID=@BranchID GROUP BY ObjectID ORDER BY SUM(Score) DESC ) AS obj,
                        (SELECT @rownum := 0 ,@rowtotal := NULL) r
                        ) AS rowobj WHERE rowobj.ObjectID=@ObjectID ");

            object ob = MySqlHelper.ExecuteScalar(sb.ToString(), parameters.ToArray());
            if (ob != null)
                rank = Convert.ToInt32(ob);

            return rank;
        }

    }
}
